Maximizing Performance
ODBC performance can be maximized by controlling the disk cache according to the recommendations below.
Maximizing Performance by Controlling Disk Cache
The oasql.ini file found in your ODBC directory contains entries that can be customized to improve performance on your computer. For example, you can control whether data is cached in memory or on disc. The Common section of the oasql.ini configuration file contains the configuration parameters used by the disk cache module of the OpenAccess SDK SQL engine.
The following fragment of a configuration file shows the [Common] section:
|
[Common] section of the oasql.ini file |
Configuration Parameters
| Parameter | Description | |
|---|---|---|
|
CacheMemSize |
Describes the threshold for result set size (in KB). If the result set size exceeds this value, then the OpenAccess SDK SQL engine starts disk caching records. |
|
|
CacheOptions |
Specifies parameters for controlling the location and size of the cache data file. It is made up of attribute=value pairs. All attribute names are upper case. |
|
|
PATH |
Describes the path to store the CacheData and Sort file(s) on disc; when missing, data will be cached in memory. |
|
|
INITIAL_SIZE |
Describes initial size of the file in megabytes (MB). Estimate this size to allow most results to be saved without having to expand it too many times. The default is 10 MB. |
|
|
INCREMENT_SIZE |
Describes the next increment size of the file (in MB). The default is 5 MB. |
|
|
MAX_SIZE |
Describes maximum allowable size of the file (in MB). The default is 50 MB. |
|
|
DATABLOCK_SIZE |
Specifies the block size of the file (in KB). This size determines how the OpenAccess SDK SQL engine writes and reads records from the cache file. The default size is 64 KB. The maximum size is 64 KB. |
|
Increasing the size of the CacheOptions parameter improves performance. For better performance, tune DATABLOCK_SIZE with the operating system file buffer size.
Cache Records to Disk
Results are cached to disk when they exceed the memory size as specified by the CacheMemSize configuration parameter. The disk cache feature will be enabled in the following cases:
- A query that includes post-processing options like ORDER BY, GROUP BY, DISTINCT clauses requires that OpenAccess SDK SQL engine retrieves the entire result set and then performs the ORDER BY or GROUP BY processing. If this result set is large, then results will be disk cached and the post-processing option will be processed on disk and results will be retrieved from disk as requested by the client application. This avoids large memory requirement.
- If the IP is not cursor-based and the result set is large, the OpenAccess SDK SQL engine will disk cache results being returned by the IP. After the IP completes returning all the results, the OpenAccess SDK SQL engine will retrieve results from disk as requested by the client application.
How Query Results Are Disk Cached
Query results are written to a CacheData file that is created uniquely for each connection using the CacheOptions configuration parameters described above. The CacheData file is created to cache results of a query and is used for all queries on that connection. The file is deleted when the user disconnects. If a user attempts to execute multiple queries that have large result sets, all of the results are stored in the same cache file.
If a result set must be sorted, two additional files, Sort1 and Sort2, are created with the same size as the CacheData file and are deleted after completing the sort operation. The server (or the Local ODBC driver) can handle one statement at a time, so only one sort operation is performed at any time. Only one set of sort files is created, even if multiple statements with large result sets require sorting.
Based on how many concurrent connections are expected that may require disk caching, you must reserve disk space sufficient for these cache files.
